package com.project.hf.models;

import com.project.hf.utils.OracleConn;

import java.sql.*;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

/**创建JDBC应用程序的步骤
 * 1.载入JDBC驱动程序
 * 2.定义连接URL
 * 3.建立连接
 * 4.创建Statement对象
 * 5.执行查询或更新
 * 6.结果处理
 * 7.关闭连接
 * Created by hp on 2016/11/10.
 */
public class dingdan_oracle {
    public static void main(String[] args) {

    }

    public  List run(){
//        String driver = "oracle.jdbc.driver.OracleDriver";
//        //2、定义连接URL
//        String url = "jdbc:oracle:thin:@192.168.43.22:1521:orcl";
//        String username = "test";//用户名
//        String password = "test";//密码
        Statement stmt = null;
        OracleConn mc1 = new OracleConn();
        Connection conn = mc1.GetOracleHFConn();


//        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        Map<String, List> map1 = new HashMap<String, List>();
        List L1 =new LinkedList();
        try {


            //4、创建statement对象，便于执行静态sql语句
            statement = conn.createStatement();

            /*动态执行SQL语句
            String updateSql = "update Users set name = ? where id = ?";
            PreparedStatement ps= connection.prepareStatement(updateSql);
            ps.setString(1, "王五");//设置第一个“？”的值
            ps.setInt(2, 1);//设置第二个“？”的值
            statement.executeUpdate(insertSql);
            */

            String sqlCol = "\"sn\",\"json\"";
            String sql = "select " + sqlCol + " from \"tableboxuse\"";
            //5、执行查询或更新操作
            resultSet = statement.executeQuery(sql);

            //6、结果处理 方式一
            while (resultSet.next()){
                String empno = resultSet.getString("sn");
                String ename = resultSet.getString("json");
                Map<String,String> tmpmap = new HashMap<String, String>();

                tmpmap.put("sn",empno);
                tmpmap.put("json",ename);
//                System.out.println(empno+" "+ename);
                L1.add(tmpmap);
            }

            //6、结果处理 方式二：比较通用，对于结果集有多列时，有好处

            //获取结果集的列数
            int columnCount = resultSet.getMetaData().getColumnCount();
            System.out.printf("共有%s列\n",columnCount);
//            for (int i = 0; i < columnCount; i++) {
//
//                Object o = resultSet.getObject(i+1);
//                System.out.print(o);
//                //System.out.printf("%15s", o);//格式化输出
//                if (i!=columnCount-1){
//                    System.out.print(",");
//                }
//            }
//            System.out.println();
            return L1;

        } catch (SQLException e) {
            System.out.println("sql err");
            e.printStackTrace();
        } catch (Exception e){
            System.out.print("Exception \n");
            e.printStackTrace();
        }
        finally {
//            //7、关闭连接
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return L1;
    }
}

